package org.shock.ss.sms.commons.db;

import java.io.File;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.struts2.ServletActionContext;
import org.shock.ss.sms.bean.system.Dept;
import org.shock.ss.sms.bean.system.User;
import org.shock.ss.sms.commons.utils.Util;

/**
 * 用于事务的db操作，注意连接不得关闭，交由事务管理器控制。所有异常上抛
 * 
 * @author 季乐
 * 
 */
public class TsDbTools {
	private static Connection conn;

	/**
	 * 利用oracle sequence 获得表主键id，防止id主键冲突 注意利用此方法必须事先在oracle中创建sequence，
	 * sequence命名规则为seq_tablename,如user表为：seq_user
	 * 
	 * @author 季乐
	 * @param tablename
	 * @param key
	 * @return long
	 * @throws Exception
	 */

	public static Long NextKey(String tablename, String key) throws Exception {
		return Long.parseLong(getString("select seq_" + tablename + ".nextval from dual"));
	}

	/**
	 * 返回一个list提供给strust2的select使用
	 * 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static List getList(String sql) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		List list = new ArrayList();
		try {
			conn = ProxoolDbPool.getConnection();

			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Map map = new HashMap();
				map.put("value", rs.getString(2));
				map.put("text", rs.getString(1));
				list.add(map);
				// throw new Exception("人工调试错误");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeStatement(stmt);

		}
		return list;
	}

	public static String getAllResult(String sql, String key) throws Exception {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String result = "";
		try {
			conn = ProxoolDbPool.getConnection();

			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result += rs.getString(key) + ",";
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);

		}
		return result + 0;
	}

	/**
	 * 输入sql语句得到一个字符串查询结果，无结果返回""
	 * 
	 * @throws Exception
	 */
	public static String getString(String sql) throws Exception {
		System.out.println(sql);
		ResultSet rs = null;
		PreparedStatement stmt = null;
		/** 待注释 */
		String resultstr = "";
		try {
			conn = ProxoolDbPool.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				resultstr = rs.getString(1);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);

		}
		return resultstr;
	}

	/** 输入sql语句得到List一个字符串查询结果，无结果返回"" */
	@SuppressWarnings("unchecked")
	public static ArrayList getStringList(String sql) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		conn = ProxoolDbPool.getConnection();
		stmt = conn.prepareStatement(sql);
		rs = stmt.executeQuery();
		ArrayList list = new ArrayList();
		while (rs.next()) {
			list.add(rs.getString(1));
		}
		ProxoolDbPool.closeResultSet(rs);
		ProxoolDbPool.closeStatement(stmt);
		return list;
	}

	/**
	 * 输入sql语句得到一个整型查询结果，无结果返回-1
	 * 
	 * @throws Exception
	 */
	public static int getInt(String sql) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		int result = -1;
		try {
			conn = ProxoolDbPool.getConnection();
			stmt = conn.prepareStatement(sql);
			System.out.println(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);

		}
		return result;
	}

	/**
	 * 执行一条算sql语句
	 * 
	 * @throws SQLException
	 */
	public static boolean doSql(String sql) throws Exception {
		PreparedStatement stmt = null;
		boolean result = true;
		try {
			conn = ProxoolDbPool.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.execute();
		} catch (Exception e) {
			result = false;
			throw e;
		} finally {
			ProxoolDbPool.closeStatement(stmt);
		}
		return result;
	}

	/**
	 * 根据sql语句得到一个map，key值就是数据库字段名，value值均为String类型
	 * 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static Map getMap(String sql) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		Map map = new HashMap();
		try {
			conn = ProxoolDbPool.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					String name = rsmd.getColumnName(i);
					String value;
					if (rsmd.getColumnTypeName(i).equalsIgnoreCase("clob")) {
						value = Util.ClobToString((Clob) rs.getClob(i));
					} else {
						value = rs.getString(i);
					}
					map.put(name.toLowerCase(), value);
				}
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeStatement(stmt);

		}
		return map;
	}

	/***************************************************************************
	 * 根据警号查询部门名称及部门编号
	 */
	public Dept getBmInfo(String bmbh) {
		Dept dep = new Dept();
		// 根据一级二级警号查询二级部门名称及编号
		String sql = "select bmmc,bmbh from  dept where id=(select (case a.sjbmid when 1 then a.id else a.sjbmid end) bmid  "
				+ "from ( select id,sjbmid from dept where id=(select bmid from "
				+ User.getTable()
				+ " where ygzh='"
				+ bmbh + "')) a )";
		// 根据一级二级三级警号查询二级部门名称及编号
		/*
		 * String sql="select bmmc ,bmbh from dept where id=(select (case sjbmid
		 * when 1 then id else sjbmid end) bmid " + "from ( select id,sjbmid
		 * from dept where id=(select (case a.sjbmid when 1 then a.id else
		 * a.sjbmid end) bmid " + "from ( select id,sjbmid from dept where
		 * id=(select bmid from "+User.getTable()+" where ygzh='"+bmbh+"'))a ))
		 * )";
		 */
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement stmt = null;
		try {
			conn = ProxoolDbPool.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				dep.setBmbh(rs.getString("bmbh"));
				dep.setBmmc(rs.getString("bmmc"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);
			ProxoolDbPool.closeConnection(conn);
		}
		return dep;
	}

	/**
	 * 根据sql语句得到一个list，key值就是数据库字段名，value值均为String类型
	 * 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static List getMapsInList(String sql) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		Map map = null;
		ArrayList list = new ArrayList();
		try {
			conn = ProxoolDbPool.getConnection();
			System.out.println(sql);
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					String name = rsmd.getColumnName(i);
					String value;
					if (rsmd.getColumnTypeName(i).equalsIgnoreCase("clob")) {
						value = Util.ClobToString((Clob) rs.getClob(i));
					} else {
						value = rs.getString(i);
					}
					map.put(name.toLowerCase(), value);
				}
				list.add(map);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeStatement(stmt);

		}
		return list;
	}

	/**
	 * 根据sql语句得到一个list，key值就是数据库字段名，value值均为String类型
	 * 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static List getMapList(String sql) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		Map map = null;
		ArrayList list = new ArrayList();
		try {
			conn = ProxoolDbPool.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				map = new HashMap();
				for (int i = 1; i <= columnCount; i++) {
					String name = rsmd.getColumnName(i);
					String value = rs.getString(i);
					map.put(name.toLowerCase(), value);
				}
				list.add(map);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeStatement(stmt);

		}
		return list;
	}

	/**
	 * 此方法是用来完成Dwr二级联动的
	 * 
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static Map getMapForSelect(String sqlStr) throws Exception {
		ResultSet rs = null;
		PreparedStatement stmt = null;
		Map dataMap = new HashMap();
		try {
			conn = ProxoolDbPool.getConnection();

			stmt = conn.prepareStatement(sqlStr);
			rs = stmt.executeQuery();
			while (rs.next()) {
				dataMap.put(Util.objToString(rs.getString(1)), rs.getString(2)); //	
			}
		} catch (Exception e) {
			throw e;
		} finally {
			ProxoolDbPool.closeResultSet(rs);
			ProxoolDbPool.closeStatement(stmt);

		}
		return dataMap;
	}

	/**
	 * 自动产生编号 author 陈建锋
	 * 
	 * @throws Exception
	 */
	public static String bhchsh(String bh1, String zd, String table) throws Exception {// 当前的年月日
																						// +后六位自动增长序号
		String bh = "";
		long bhend = 0;
		bh = getString("select  max(a) from (select TO_NUMBER( substr(" + zd + ",length(bh1)-5,length(" + zd
				+ "))) a  from  " + table + "  where " + zd + ">0 )");
		if (bh != null && !"".equals(bh)) {
			bhend = Long.parseLong(bh);
		}
		bhend = bhend + 1;
		bh = "000000" + bhend;
		bh = bh.substring(bh.length() - 6, bh.length());
		// 取得当前时间
		// String dates=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		bh = new SimpleDateFormat("yyyy-MM-dd").format(new Date()) + bh;
		bh = bh1 + bh;
		bh = bh.replaceAll("-", "");
		return bh;
	}

	/**
	 * 传一路径和一文件名删除一文件 例如： delfile("/upload/movies","1232342242.rar")
	 * 
	 * @author qz
	 * @since 2010-09-20
	 */
	public static void delfile(String path, String filename) {
		File delFile = new File(ServletActionContext.getServletContext().getRealPath(path) + "\\" + filename);
		delFile.delete();
	}
}
